<?php

namespace App\Console\Commands;

use App\Model\ChannelStatistics;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class GenerateChannelStatisticsCommand extends Command
{
    private $model = null;
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'generate:channel_statistics {type=0}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct(ChannelStatistics $channelStatistics)
    {
        parent::__construct();
        $this->model = $channelStatistics;
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $type = (int) $this->argument('type');
        if($type === 0){
            $this->generateYesterdayData();
        } elseif ($type === 1){
            $this->generateAll();
        }
    }
    
    private function generateYesterdayData(){
        $sql = "SELECT
	u_r.channel_id,
	DATE_SUB(CURDATE(), INTERVAL 1 DAY) created_date,
	COUNT(
		DISTINCT
		IF (
			f_r.`status` = 1
			AND FROM_UNIXTIME(
				f_r.pay_timestamp,
				'%Y-%m-%d'
			) = DATE_SUB(CURDATE(), INTERVAL 1 DAY),
			u_r.user_id,
			NULL
		)
	) new_recharge_user,
	SUM(

		IF (
			f_r.`status` = 1
			AND FROM_UNIXTIME(
				f_r.pay_timestamp,
				'%Y-%m-%d'
			) = DATE_SUB(CURDATE(), INTERVAL 1 DAY),
			f_r.price,
			0
		)
	) new_recharge_price,
	COUNT(DISTINCT u_r.user_id) register_user
FROM
	zb_user_register_log u_r
LEFT JOIN (
	SELECT
		FROM_UNIXTIME(pay_timestamp,'%Y-%m-%d') created_at,
		ANY_VALUE(price) price,
		user_id,
		`status`,
		ANY_VALUE(pay_timestamp) pay_timestamp
	FROM
			zb_finance_recharge_order
	WHERE
		`status` = 1
	AND
		pay_timestamp BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY))
	AND UNIX_TIMESTAMP(CURDATE())
	GROUP BY
		created_at,user_id
) f_r ON f_r.user_id = u_r.user_id
WHERE
	u_r.time BETWEEN UNIX_TIMESTAMP(
		DATE_SUB(CURDATE(), INTERVAL 1 DAY)
	)
AND UNIX_TIMESTAMP(CURDATE())
GROUP BY
	u_r.channel_id";
        $yesterday_data = DB::connection('api')->select($sql);
        if(empty($yesterday_data)){
            if($this->model->query()->firstOrCreate([
                'channel_id'=> 0,
                'register_user' => 0,
                'new_recharge_user' => 0,
                'new_recharge_price' => 0,
                'grand_register' => 0,
                'grand_recharge_user' => 0,
                'grand_recharge' => 0,
                'arpu' => 0,
                'created_date' =>date('Y-m-d', strtotime('-1 day'))
            ])) {
                $this->info('ok');
            } else {
                $this->info('fail');
            }
        } else {
            
            // 获取每个渠道的最新一条记录
            $pref_result = DB::connection('mysql')->select("SELECT channel_id,grand_register,grand_recharge_user,grand_recharge FROM cms_channel_statistics WHERE id IN(SELECT MAX(id) FROM cms_channel_statistics GROUP BY channel_id)");
            $pref_result = json_decode(json_encode($pref_result), true);
            $pref_result = array_column($pref_result,null, 'channel_id');
            // 计算当前渠道商所有已注册用户得充值金额(非新注册充值金额)
            $grand_price_data = $this->statisticsChannelRecharge(true);

            $s_time = strtotime(date("Y-m-d 00:00:00",strtotime("-1 day"))) ;
            $e_time = strtotime(date("Y-m-d 23:59:59",strtotime("-1 day"))) ;
            $insert_data = [];
            foreach ($yesterday_data as $row){
                if(!$row->channel_id) continue;
                // 计算当前渠道商所有已注册用户的充值金额(非新注册充值金额)
                $grand_recharge_price = 0;
                if(isset($grand_price_data[$row->created_date]) && isset($grand_price_data[$row->created_date][$row->channel_id])){
                    $recharge_price = collect($grand_price_data[$row->created_date][$row->channel_id])->pluck('recharge_price')->toArray();
                    $grand_recharge_price += $recharge_price[0];
                }

                $rechage_sql = "select a.user_id,channel_id,IFNULL(b.price,0) as price from zb_user_register_log a left join
                        (select user_id,sum(price) as price from zb_finance_recharge_order where pay_timestamp < {$e_time} AND status = 1 group by user_id) b
                        on a.user_id = b.user_id  where time < {$e_time} AND channel_id = ".$row->channel_id;
                $rechage_data = objToArray(DB::connection('api')->select($rechage_sql));
                // 累计充值金额
                $grand_recharge = array_sum(array_column($rechage_data,'price'));
                // 累计注册人数
                $grand_register =  count($rechage_data);
                // 累计充值人数
                $grand_recharge_user = count(array_filter(array_column($rechage_data,'price')));
                
                // 人均 ARPU
                if($grand_register === 0){
                    $arpu = $grand_recharge;
                } else {
                    $arpu = $grand_recharge / $grand_register;
                }
                $sql = "select  sum(price) as sum_price from zb_finance_recharge_order
                where status = 1 and channel_id = $row->channel_id and pay_timestamp BETWEEN $s_time and $e_time limit 1";
                $day_recharge = DB::connection('api')->select($sql);
                $day_recharge_price = $day_recharge[0]->sum_price ?? 0;
                $sql2 = "select  count(user_id) as count_user from zb_finance_recharge_order
                where status = 1 and channel_id = $row->channel_id and pay_timestamp BETWEEN $s_time and $e_time limit 1";
                $day_recharge_user = DB::connection('api')->select($sql2);
                $day_recharge_user_num = $day_recharge_user[0]->count_user ?? 0;
                array_push($insert_data,collect($row)->merge([
                    'grand_register' => $grand_register,
                    'grand_recharge_user' => $grand_recharge_user,
                    'grand_recharge' => $grand_recharge,
                    'day_recharge_price' => $day_recharge_price,
                    'day_recharge_user' => $day_recharge_user_num,
                    'arpu' => $arpu
                ])->toArray());
            }
            if($this->model->batchInsert('channel_statistics', $insert_data)){
                $this->info('ok');
            } else {
                $this->info('fail');
            }
        }
    }
    
    private function generateAll(){
        // 最小日期
        $min_date_res = DB::connection('api')->selectOne("SELECT DATE_SUB(MIN(FROM_UNIXTIME(create_timestamp,'%Y-%m-%d')), INTERVAL 1 DAY) AS created_at FROM zb_user WHERE create_timestamp>0");
        if(!$min_date_res) return [];
        $sql = "SELECT
	t.created_at created_date,
	COALESCE (c_u.channel_id, 0) channel_id,
	COUNT(DISTINCT c_u.user_id) register_user,
	COUNT(
		DISTINCT
		IF (
			c_u.`status` = 1
			AND c_u.created_at = FROM_UNIXTIME(
				c_u.pay_timestamp,
				'%Y-%m-%d'
			),
			c_u.user_id,
			NULL
		)
	) new_recharge_user,
	SUM(

		IF (
			c_u.`status` = 1
			AND c_u.created_at = FROM_UNIXTIME(
				c_u.pay_timestamp,
				'%Y-%m-%d'
			),
			c_u.price,
			0
		)
	) new_recharge_price
FROM
	(
		SELECT
			@num :=@num + 1,
			DATE_FORMAT(
				adddate(
					'{$min_date_res->created_at}',
					INTERVAL @num DAY
				),
				'%Y-%m-%d'
			) AS created_at
		FROM
			zb_user_register_log,
			(SELECT @num := 0) t
		WHERE
			ADDDATE(
				'{$min_date_res->created_at}',
				INTERVAL @num DAY
			) < DATE_SUB(CURDATE(), INTERVAL 2 DAY)
		ORDER BY
			created_at ASC
	) t
INNER JOIN (
	SELECT
		f_r.price,
		f_r.`status`,
		f_r.created_at pay_at,
		FROM_UNIXTIME(u_r.time, '%Y-%m-%d') created_at,
		u_r.channel_id,
		u_r.user_id,
		f_r.pay_timestamp
	FROM
		zb_user_register_log u_r
	LEFT JOIN (
		SELECT
			ANY_VALUE(pay_timestamp) pay_timestamp,
			FROM_UNIXTIME(pay_timestamp, '%Y-%m-%d') created_at,
			SUM(price) price,
			user_id,
			`status`
		FROM
			zb_finance_recharge_order
		WHERE
			`status` = 1
		GROUP BY
			created_at,
			user_id
	) f_r ON u_r.user_id = f_r.user_id
) c_u ON c_u.created_at = t.created_at
GROUP BY
	t.created_at,
	c_u.channel_id";
        
        $grand_price_data = $this->statisticsChannelRecharge();
        $channel_grand_register = [];        // 渠道累计注册
        $channel_grand_recharge_user = [];   // 渠道累计充值人数
        $channel_grand_recharge = [];        // 渠道累计充值
        $arpu = 0;                          // 人均ARPU
        $insert_data = [];
        foreach (DB::connection('api')->cursor($sql) as $row){
            if(!isset($channel_grand_register[$row->channel_id])) $channel_grand_register[$row->channel_id] = 0;
            if(!isset($channel_grand_recharge_user[$row->channel_id])) $channel_grand_recharge_user[$row->channel_id] = 0;
            if(!isset($channel_grand_recharge[$row->channel_id])) $channel_grand_recharge[$row->channel_id] = 0;
            $channel_grand_register[$row->channel_id] += $row->register_user;
            $channel_grand_recharge_user[$row->channel_id] += $row->new_recharge_user;
            if(isset($grand_price_data[$row->created_date]) && isset($grand_price_data[$row->created_date][$row->channel_id])){
                $recharge_price = collect($grand_price_data[$row->created_date][$row->channel_id])->pluck('recharge_price')->toArray();
                $channel_grand_recharge[$row->channel_id] += $recharge_price[0];
            }
            if($channel_grand_recharge[$row->channel_id] === 0){
                $arpu = 0;
            } else {
                $arpu = $channel_grand_recharge[$row->channel_id] / $channel_grand_register[$row->channel_id];
            }
            array_push($insert_data,collect($row)->merge([
                'grand_register' => $channel_grand_register[$row->channel_id],
                'grand_recharge_user' => $channel_grand_recharge_user[$row->channel_id],
                'grand_recharge' => $channel_grand_recharge[$row->channel_id],
                'arpu' => $arpu
            ])->toArray());
        }
        
        if($this->model->batchInsert('channel_statistics', $insert_data)){
            $this->info('ok');
        } else {
            $this->info('fail');
        }
    }
    
    private function statisticsChannelRecharge($date = false){
        $sql = "SELECT
	u_r.channel_id,
	f_r.pay_at,
	SUM(f_r.price) recharge_price
FROM
	zb_user_register_log u_r
INNER JOIN (
	SELECT
		FROM_UNIXTIME(pay_timestamp,'%Y-%m-%d') pay_at,
		user_id,
		SUM(price) price
	FROM
		zb_finance_recharge_order
	WHERE `status`=1";
        
        if($date === true){
            $sql .= " AND pay_timestamp BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY)) AND UNIX_TIMESTAMP(CURDATE())";
        }
        
        $sql .="
	GROUP BY
		pay_at,
		user_id
) f_r ON u_r.user_id=f_r.user_id
GROUP BY
	f_r.pay_at,
	u_r.channel_id";
        
        $res = DB::connection('api')->select($sql);
        $res = json_decode(json_encode($res), true);
        $data = [];
        if(!empty($res)) {
            $data = collect($res)->groupBy(['pay_at',function($item){
                return $item['channel_id'];
            }], true)->toArray();
        }
        return $data;
    }
}
